<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Work_facility_model extends CI_Model 
{
	function __construct()
	{
		parent:: __construct();
	}
		
	/*============================================================================================================
	ID CARD AND TELEPHONE
	
	Name getWorkFacility
	
	============================================================================================================*/
	function getWorkPermission()
	{
		$sql =" SELECT SIK_ID, VENDOR_PIC, TO_CHAR(SIK_SDATE,'DD/MM/YYYY') SIK_SDATE, TO_CHAR(SIK_EDATE, 'DD/MM/YYYY') SIK_EDATE 
				FROM FM_SIK
				WHERE SIK_CLOSING IS NULL AND SIK_ATASAN_APP = 'Y' AND SIK_GA_APP = 'Y'";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0){return $Udata;}else{return FALSE;}
	}
	
	function arr_floor()
	{
		$sql = "SELECT distinct flo.mst_floor_desc
			 	FROM fm_mst_floor flo
			 	WHERE flo.mst_floor_status = 'Y' 
				ORDER BY flo.mst_floor_desc";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0){return $Udata;}else{return FALSE;}
	}
	
	function count_all_meeting()
	{
		$sql = "SELECT count(*) AS total_row
				FROM lkk_vendor_mst mstv 
				INNER JOIN lkk_vendor_site sitev on mstv.VENDOR_ID = sitev.VENDOR_ID";
		$result = $this->db->query($sql);
		$Udata = $result->row('TOTAL_ROW');
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_paged_mailing($limit = 10, $offset = 0)
	{
		$sql = "SELECT ROWNUM, lgs.HR_ID,lgs.USERNAME,lgs.ACTIVE_FLAG,nvl((SELECT grp.group_name FROM tbl_groups grp WHERE grp.hr_id = lgs.hr_id),'VENDOR') AS GROUP_NAME,
				nvl((SELECT vmst.VENDOR_NAME FROM LKK_VENDOR_SITE vsite INNER JOIN LKK_VENDOR_MST vmst ON vsite.vendor_id = vmst.vendor_id 
				WHERE vsite.vendor_site_id = lgs.vendor_site_id),'Telkomsel') AS VENDOR_NAME,
				lgs.VENDOR_SITE_ID
				FROM logins lgs
				WHERE ROWNUM <= ".$limit;
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0){return $Udata;}else{return FALSE;}
	}
	
	function count_all_mailing()
	{
		$sql = "SELECT count(*) AS total_row
				FROM lkk_vendor_mst mstv 
				INNER JOIN lkk_vendor_site sitev on mstv.VENDOR_ID = sitev.VENDOR_ID";
		$result = $this->db->query($sql);
		$Udata = $result->row('TOTAL_ROW');
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_paged_searchRoom($room_id = 0,$limit = 10, $offset = 0)
	{
		if($room_id == 0)
		{
			$cond = ""; 
		}
		else
		{
			$cond = "AND mst_cat.MST_CATEGORY_ID = ".$room_id;
		}
		$sql = "SELECT
				mst_cat.MST_CATEGORY_ID,
				mst_flo.MST_FLOOR_DESC,
				mst_cat.MST_CATEGORY_DESC,
				nvl(mst_cat.MST_CATEGORY_CAPACITY,0) MST_CATEGORY_CAPACITY
				FROM fm_mst_category mst_cat
				INNER JOIN fm_mst_floor mst_flo ON mst_flo.MST_FLOOR_ID = mst_cat.MST_FLOOR_ID AND mst_flo.MST_TYPE_ID = mst_cat.MST_TYPE_ID
				WHERE mst_cat.MST_CATEGORY_STATUS = 'Y' ".$cond." ORDER BY mst_flo.MST_FLOOR_DESC ASC";
		$sql_new = $this->db->_limit($sql,$limit,$offset);
		$result = $this->db->query($sql_new);		
//		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{return $Udata;}
		else{return FALSE;}
	}

	function count_all_searchRoom($room_id = 0, $limit=0, $offset=0)
	{
		if($room_id == 0)
		{
			$cond = ""; 
		}
		else
		{
			$cond = "AND mst_cat.MST_CATEGORY_ID = ".$room_id;
		}
		$sql = "SELECT
				count(*) AS TOTAL_ROW
				FROM fm_mst_category mst_cat
				INNER JOIN fm_mst_floor mst_flo ON mst_flo.MST_FLOOR_ID = mst_cat.MST_FLOOR_ID AND mst_flo.MST_TYPE_ID = mst_cat.MST_TYPE_ID
				WHERE mst_cat.MST_CATEGORY_STATUS = 'Y' ".$cond;
		$result = $this->db->query($sql);
		$Udata = $result->row('TOTAL_ROW');
		$Rdata = $result->num_rows();
		if ($Rdata > 0){return $Udata;}else{return FALSE;}
	}
	
	/*================================================================
	Function to Get Meeting Room Floor
	Last Edit : 22/05/2014
	By : Ery
	=================================================================*/
	function getFloor()
	{
		/*
		$sql = "SELECT
			distinct mst_flo.MST_FLOOR_DESC
			FROM fm_mst_category mst_cat
			INNER JOIN fm_mst_floor mst_flo ON mst_flo.MST_FLOOR_ID = mst_cat.MST_FLOOR_ID AND mst_flo.MST_TYPE_ID = mst_cat.MST_TYPE_ID
			WHERE mst_cat.MST_CATEGORY_STATUS = 'Y' ORDER BY mst_flo.MST_FLOOR_DESC ASC
		*/
		$sql = "select distinct mst_floor_desc 
			from fm_mst_floor 
			where mst_floor_status='Y' 
			order by mst_floor_desc asc    
		";		
		$result = $this->db->query($sql);
		$Udata = $result->result_array('MST_FLOOR_DESC');
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getManager()
	{
		$sql = "
		select hr_id, name from 
			( 
				select a.hr_id, b.name from logins a, hrs b 
				where a.hr_id = b.id 

				and (upper(b.job) in (select job from job_approval)) 
				and upper(b.city)='JAKARTA' 

				order by name asc 
			)    
		";		
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getFloorForm($room_name)
	{
		$sql = " 
				SELECT f.MST_FLOOR_DESC FROM fm_mst_category c
				INNER JOIN fm_mst_floor f ON f.MST_FLOOR_ID = c.MST_FLOOR_ID 
				AND f.MST_TYPE_ID = c.MST_TYPE_ID 
				WHERE c.MST_CATEGORY_STATUS = 'Y' 
				AND c.MST_CATEGORY_ID = '$room_name'  
		";
		
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0){return $Udata;}else{return FALSE;}
	}
	
	function getRoom()
	{
		$sql = "SELECT
				mst_cat.MST_CATEGORY_ID,
				mst_flo.MST_FLOOR_DESC,
				mst_cat.MST_CATEGORY_DESC,
				nvl(mst_cat.MST_CATEGORY_CAPACITY,0) MST_CATEGORY_CAPACITY
				FROM fm_mst_category mst_cat
				INNER JOIN fm_mst_floor mst_flo ON mst_flo.MST_FLOOR_ID = mst_cat.MST_FLOOR_ID AND mst_flo.MST_TYPE_ID = mst_cat.MST_TYPE_ID
				WHERE mst_cat.MST_CATEGORY_STATUS = 'Y' ORDER BY mst_flo.MST_FLOOR_DESC ASC";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			foreach ($Udata as $row)
        	{
				//echo $row['MST_CATEGORY_ID'];
            	$U_data[0]= '--Select Meeting Room--';
           		$U_data[$row['MST_CATEGORY_ID']] = "Seat: ".$row['MST_CATEGORY_CAPACITY']." :Floor. ".$row['MST_FLOOR_DESC']." - ".$row['MST_CATEGORY_DESC'];
        	}
			return $U_data;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getRoomForm($type_name)
	{
		$qry  = " select key, value, floor_desc, gab from ( 
					select key, value, floor_desc, gab, rownum row_no from ( 
					select key, value, floor_desc, gab from ( 
					select mascateg.MST_CATEGORY_ID as key,
						  mascateg.MST_CATEGORY_DESC as value,
					(select flo.MST_FLOOR_DESC
						   from fm_mst_floor flo
						   where flo.MST_FLOOR_ID = mascateg.MST_FLOOR_ID) as floor_desc,
					(select flo.MST_FLOOR_DESC
						   from fm_mst_floor flo
						   where flo.MST_FLOOR_ID = mascateg.MST_FLOOR_ID)||' - '|| mascateg.MST_CATEGORY_DESC gab
				  from FM_MST_CATEGORY mascateg where mascateg.mst_CATEGORY_status = 'Y' and mascateg.MST_TYPE_ID = $type_name) 
						   where 1=1)) 				  
				";
		$result = $this->db->query($qry);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0){return $Udata;}else{return FALSE;}
	}
	
	function getDetail($id)
	{
		$sql = "SELECT sitev.VENDOR_ID,sitev.VENDOR_SITE_ID,mstv.VENDOR_NAME,
				sitev.VENDOR_SITE_CODE,sitev.VENDOR_PHONE,sitev.PIC,sitev.PIC_PHONE,sitev.EMAIL
				FROM lkk_vendor_mst mstv 
				INNER JOIN lkk_vendor_site sitev on mstv.VENDOR_ID = sitev.VENDOR_ID
				WHERE mstv.VENDOR_ID = ".$id."
				ORDER BY mstv.VENDOR_NAME ASC";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0){return $Udata;}else{return FALSE;}
	}
	
	function getTypeList()
	{
		$sql = "SELECT
				MST_TYPE_ID, MST_TYPE_DESC FROM FM_MST_TYPE 
				WHERE MST_TYPE_STATUS = 'Y' ORDER BY MST_TYPE_DESC ";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getReference(){
        $sql 	= "
                select sik_id, vendor_pic, to_char(SIK_SDATE,'DD/MM/YYYY') SIK_SDATE, to_char(SIK_EDATE, 'DD/MM/YYYY') SIK_EDATE 
                from fm_sik
                order by vendor_pic asc
        ";    
        //echo $sql;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
	
	function getVendor()
	{
		$sql = "
			select vendor_id, vendor_name from lkk_vendor_mst order by vendor_name asc  
		";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0){return $Udata;}else{return FALSE;}
	}
	
	function getVendorForm($pVendorId)
	{
		$sql = "
			select vendor_id, vendor_name, pic, vendor_source from lkk_vendor_mst 
			where vendor_id = $pVendorId 
			order by vendor_name asc  
		";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0){return $Udata;}else{return FALSE;}
	}
	
	function insert_ot($arr){
		$pNik 			= $arr['pNik'];
		$pSdate 		= $arr['pSdate'];    			
		$pEdate 		= $arr['pEdate'];    			
		$pStime 		= $arr['pStime'];   		    
		$pEtime 		= $arr['pEtime'];   		    
		$pAc 			= $arr['pAc'];    			   
		$pLift 			= $arr['pLift'];    			
		$pLighting 		= $arr['pLighting'];  		  
		$pDescription 	= $arr['pDescription'];  		
		$pAtasanNik 	= $arr['pAtasanNik'];  		
		$pAtasanNama 	= $arr['pAtasanNama'];  		
		$pNikFor 		= $arr['pNikFor'];    		
		
		//UNTUK ISIAN TABEL BAWAH
		
		//Baris-1
		$OT_Name1 			= $arr['OT_Name1'];           
		$OT_Floor1 			= $arr['OT_Floor1'];          
		$OT_Location1 		= $arr['OT_Location1'];        
		$OT_VendorId1 		= $arr['OT_VendorId1'];        
		$OT_VendorName1 	= $arr['OT_VendorName1'];      
		$OT_VendorPic1 		= $arr['OT_VendorPic1'];		
		
		//Baris-2
		$OT_Name2 			= $arr['OT_Name2'];           
		$OT_Floor2 			= $arr['OT_Floor2'];          
		$OT_Location2 		= $arr['OT_Location2'];        
		$OT_VendorId2 		= $arr['OT_VendorId2'];        
		$OT_VendorName2 	= $arr['OT_VendorName2'];      
		$OT_VendorPic2 		= $arr['OT_VendorPic2']; 
		
		//Baris-3
		$OT_Name3 			= $arr['OT_Name3'];           
		$OT_Floor3 			= $arr['OT_Floor3'];          
		$OT_Location3 		= $arr['OT_Location3'];        
		$OT_VendorId3 		= $arr['OT_VendorId3'];        
		$OT_VendorName3 	= $arr['OT_VendorName3'];      
		$OT_VendorPic3 		= $arr['OT_VendorPic3'];
		
		//Baris-4
		$OT_Name4 			= $arr['OT_Name4'];           
		$OT_Floor4 			= $arr['OT_Floor4'];          
		$OT_Location4 		= $arr['OT_Location4'];        
		$OT_VendorId4 		= $arr['OT_VendorId4'];        
		$OT_VendorName4 	= $arr['OT_VendorName4'];      
		$OT_VendorPic4 		= $arr['OT_VendorPic4'];
		
		//Baris-5
		$OT_Name5 			= $arr['OT_Name5'];           
		$OT_Floor5 			= $arr['OT_Floor5'];          
		$OT_Location5 		= $arr['OT_Location5'];        
		$OT_VendorId5 		= $arr['OT_VendorId5'];        
		$OT_VendorName5 	= $arr['OT_VendorName5'];      
		$OT_VendorPic5 		= $arr['OT_VendorPic5'];
		
		$qry1 = " 
			select max(to_number(substr(so_id,1,5),'99999')) so_id_old, 
			rtrim(to_char(sysdate,'RM')) romawi from fm_so 
		";
		$a = $this->db->query($qry1)->row_array();
		
		$cSoIdOld = $a["SO_ID_OLD"];	
		$cRomawi  = $a["ROMAWI"];	
		$cSoIdNew = $cSoIdOld +1;
		
		$qry2 = " 
			select lpad($cSoIdNew,5,0)||'/OVER/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') so_id from dual 
		";
		
		$b = $this->db->query($qry2)->row_array();
		
		$cSoId = $b["SO_ID"];
		
		$stmt	=	" insert into fm_so (so_id, so_nip, so_untuk_nik, so_sdate, so_edate, so_shour, so_ehour,
									   so_description, so_ac, so_lift, so_lighting, 
									   so_atasan_nik, so_reference, dtmupd ) ". 
									   
					" values ('$cSoId',$pNik,$pNikFor,to_date('$pSdate','dd/mm/yyyy'),to_date('$pEdate','dd/mm/yyyy'),'$pStime','$pEtime', 
									   '$pDescription','$pAc','$pLift','$pLighting',
									   $pAtasanNik, '$pReqId', sysdate )  
									   
					";
		
		$this->db->query($stmt);	
		
		//Baris-1
		if(!empty($OT_Name1)){		
		
			$sql1=" insert into fm_so_det( 
								sod_name, so_id, sod_floor, sod_location, sod_vendor_id, sod_vendor_name )   
					values( 
								'$OT_Name1', '$cSoId', '$OT_Floor1', '$OT_Location1', '$OT_VendorId1', '$OT_VendorName1' ) 
					";
					
			$this->db->query($sql1);						
		}
		
		//Baris-2
		if(!empty($OT_Name2)){		
		
			$sql2=" insert into fm_so_det( 
								sod_name, so_id, sod_floor, sod_location, sod_vendor_id, sod_vendor_name )   
					values( 
								'$OT_Name2', '$cSoId', '$OT_Floor2', '$OT_Location2', '$OT_VendorId2', '$OT_VendorName2' ) 
					";
					
			$this->db->query($sql2);						
		}
		
		//Baris-3
		if(!empty($OT_Name3)){		
		
			$sql1=" insert into fm_so_det( 
								sod_name, so_id, sod_floor, sod_location, sod_vendor_id, sod_vendor_name )   
					values( 
								'$OT_Name3', '$cSoId', '$OT_Floor3', '$OT_Location3', '$OT_VendorId3', '$OT_VendorName3' ) 
					";
					
			$this->db->query($sql3);						
		}
		
		//Baris-4
		if(!empty($OT_Name4)){		
		
			$sql1=" insert into fm_so_det( 
								sod_name, so_id, sod_floor, sod_location, sod_vendor_id, sod_vendor_name )   
					values( 
								'$OT_Name4', '$cSoId', '$OT_Floor4', '$OT_Location4', '$OT_VendorId4', '$OT_VendorName4' ) 
					";
					
			$this->db->query($sql4);						
		}
		
		//Baris-5
		if(!empty($OT_Name5)){		
		
			$sql1=" insert into fm_so_det( 
								sod_name, so_id, sod_floor, sod_location, sod_vendor_id, sod_vendor_name )   
					values( 
								'$OT_Name5', '$cSoId', '$OT_Floor5', '$OT_Location5', '$OT_VendorId5', '$OT_VendorName5' ) 
					";
					
			$this->db->query($sql5);						
		}
		
		$msg = "FM : Order No $cSoId telah berhasil, untuk Proses selanjutnya menunggu Approval dari atasan anda $pAtasanNama.";
		$msgatasan = "FM : Order No $cSoId membutuhkan approval anda.";

		return $msg;
		
		//$msisdn = dbora_getgamsisdn($pNikFor);
		//send_sms($msisdn, $msg);

		//$msisdnatasan = dbora_getgamsisdn($pAtasanNik);
		//send_sms($msisdnatasan, $msgatasan);
					
		
	}
	
	function insert_rm($arr) {
        $r_id 	= $arr['r_id'];
		$r_name	= $arr['r_nama'];
		$r_mgr	= $arr['r_mgr'];
		$r_mgrid	= $arr['r_mgrid'];
		$r_dept	= $arr['r_dept'];
		$r_job	= $arr['r_job'];		        
		$floor	= $arr['floor'];
		$serial	= $arr['serial'];
		$Desc	= $arr['Desc'];
		
		$G_Date1	= $arr['G_Date1'];
		$G_Date2	= $arr['G_Date2'];
		
		$type_name	= $arr['type_name'];
		$room_name	= $arr['room_name'];
		
		//$login = $this->session->userdata('login');
        //$user = $login['HR_ID'];

        //$sql = "insert into fm_mst_messenger(messenger_id, messenger_name, messenger_phone, messenger_status, create_by, create_date) 
			//	values (SEQ_MESSENGER.nextval, '$messengername', '$messengerphone', '$messengerstatus', '$user', sysdate)";
        //$this->db->query($sql);
		
		$qry1 = " 
			select max(to_number(substr(rm_id,1,5),'99999')) rm_id_old, 
			rtrim(to_char(sysdate,'RM')) romawi from fm_rm 
		";
		$a = $this->db->query($qry1)->row_array();
		
		$cRmIdOld = $a["RM_ID_OLD"];	
		$cRomawi  = $a["ROMAWI"];	
		$cRmIdNew = $cRmIdOld +1;
		
		$qry2 = " 
			select lpad($cRmIdNew,5,0)||'/DOC/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') rm_id from dual 
		";
		
		$b = $this->db->query($qry2)->row_array();
		
		$cRmId = $b["RM_ID"];
		
		$stmt	=	" insert into fm_rm (rm_id, rm_nip, rm_description, rm_suite, rm_date,
									   rm_create_by, rm_create_date, rm_serial, rm_floor,
									   rm_type, rm_category, rm_delivery, rm_return, 
									   RM_ATASAN_ID, RM_UNTUK_NIK) ". 
									   
					" values ('$cRmId','$r_id','$Desc','$r_name', trunc(sysdate), 
									   '', trunc(sysdate), '$serial', '$floor',
									   '$type_name','$room_name', to_date('$G_Date1','dd/mm/yyyy'), to_date('$G_Date2','dd/mm/yyyy'),
									   '$r_mgr', '$r_id') 
									   
					";
					
		$this->db->query($stmt);			
					
		$msg = "FM : Order No $cRmId telah berhasil, untuk Proses selanjutnya menunggu Approval dari atasan anda $r_mgr.";
		$msgatasan = "FM : Order No $cRmId membutuhkan approval anda."; 
		
		return $msg;
		
		//Sms to User
		//$msisdn = dbora_getgamsisdn($pNikFor);
		//send_sms($msisdn, $msg);
		//End Sms User
	
		//Sms to Atasan
		//$msisdnatasan = dbora_getgamsisdn($pTrAtasanNik);
		//send_sms($msisdnatasan, $msgatasan);
		//End Sms Atasan							   
									   	   									   												  
    }
}